package com.pineapple.module.core.user.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.pineapple.module.core.user.domain.User;
import com.pineapple.module.core.user.domain.UserCriteria;
import com.pineapple.module.core.user.domain.UserSearch;

public class UserDAO {
	protected int count(UserCriteria criteria,Connection conn) throws Exception{
		int totRecord =0;

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) AS TOT FROM user ");
		sql.append("WHERE 1=1 ");

		if(criteria.getUserId() !=null && !criteria.getUserId().equals("")){
			sql.append("AND user_id='"+criteria.getUserId()+"' ");
		}
		if(criteria.getName() !=null && !criteria.getName().equals("")){
			sql.append("AND user_fname='"+criteria.getName()+"' ");
		}
		if(criteria.getSurName() !=null && !criteria.getSurName().equals("")){
			sql.append("AND user_lname='"+criteria.getSurName()+"' ");
		}
		if(criteria.getUserName()!=null && !criteria.getUserName().equals("")){
			sql.append("AND user_name='"+criteria.getUserName()+"' ");
		}
		if(criteria.getPeopleId()!=null && !criteria.getPeopleId().equals("")){
			sql.append("AND user_idcard='"+criteria.getPeopleId()+"' ");
		}
		if(criteria.getPhone()!=null && !criteria.getPhone().equals("")){
			sql.append("AND user_landlines='"+criteria.getPhone()+"' ");
		}
		if(criteria.getMobile()!=null && !criteria.getMobile().equals("")){
			sql.append("AND user_mobile='"+criteria.getMobile()+"' ");
		}
		if(criteria.getStatus()!=null && !criteria.getStatus().equals("")){
			sql.append("AND user_active='"+criteria.getStatus()+"' ");
		}
		try(Statement stmt = conn.createStatement()){
			ResultSet rst = stmt.executeQuery(sql.toString());
			if (rst.next()) {
				totRecord = rst.getInt("TOT");
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return totRecord;
	}

	protected List<UserSearch> search(UserCriteria criteria,Connection conn) throws Exception{
		List<UserSearch> lstUser = new ArrayList<UserSearch>();
		UserSearch user = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM user ");
		sql.append("WHERE 1=1 ");

		if(criteria.getUserId() !=null && !criteria.getUserId().equals("")){
			sql.append("AND user_id='"+criteria.getUserId()+"' ");
		}
		if(criteria.getName() !=null && !criteria.getName().equals("")){
			sql.append("AND user_fname='"+criteria.getName()+"' ");
		}
		if(criteria.getSurName() !=null && !criteria.getSurName().equals("")){
			sql.append("AND user_lname='"+criteria.getSurName()+"' ");
		}
		if(criteria.getUserName()!=null && !criteria.getUserName().equals("")){
			sql.append("AND user_name='"+criteria.getUserName()+"' ");
		}
		if(criteria.getPeopleId()!=null && !criteria.getPeopleId().equals("")){
			sql.append("AND user_idcard='"+criteria.getPeopleId()+"' ");
		}
		if(criteria.getPhone()!=null && !criteria.getPhone().equals("")){
			sql.append("AND user_landlines='"+criteria.getPhone()+"' ");
		}
		if(criteria.getMobile()!=null && !criteria.getMobile().equals("")){
			sql.append("AND user_mobile='"+criteria.getMobile()+"' ");
		}
		if(criteria.getStatus()!=null && !criteria.getStatus().equals("")){
			sql.append("AND user_active='"+criteria.getStatus()+"' ");
		}

		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());

		        while(rs.next()) {
		        	user= new UserSearch();
		        	user.setUserId(rs.getString("user_id"));
		        	user.setUserName(rs.getString("user_name"));
		        	user.setName(rs.getString("user_fname"));
		        	user.setSurName(rs.getString("user_lname"));
		        	user.setPassword(rs.getString("user_password"));
		        	user.setAddress(rs.getString("user_address"));
		        	user.setPhone(rs.getString("user_landlines"));
		        	user.setMobile(rs.getString("user_moblie"));
		        	user.setPeopleId(rs.getString("user_idcard"));
		        	user.setStatus(rs.getString("user_active"));
		        	lstUser.add(user);
		        }


		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstUser;
	}



	protected void add(Connection conn, User user)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO user (user_name,user_fname,user_lname,user_password,user_address,user_landlines,user_moblie,user_idcard,user_active)");
		sql.append("VALUES (");


		sql.append("'"+user.getUserName()+"' ");
			sql.append(",'"+user.getName()+"' ");
			sql.append(",'"+user.getSurName()+"' ");
			sql.append(",'"+user.getPassword()+"' ");
			sql.append(",'"+user.getAddress()+"' ");
			sql.append(",'"+user.getPhone()+"' ");
			sql.append(",'"+user.getMobile()+"' ");
			sql.append(",'"+user.getPeopleId()+"' ");
			sql.append(",'1' ");
			sql.append(" )");

			System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}
	protected void edit(Connection conn, User user)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE user SET ");
		sql.append("user_name='"+user.getUserName()+"' ");
		sql.append(",user_fname='"+user.getName()+"' ");
		sql.append(",user_lname='"+user.getSurName()+"' ");
		sql.append(",user_password='"+user.getPassword()+"' ");
		sql.append(",user_address='"+user.getAddress()+"' ");
		sql.append(",user_landlines='"+user.getPhone()+"' ");
		sql.append(",user_moblie='"+user.getMobile()+"' ");
		sql.append(",user_idcard='"+user.getPeopleId()+"' ");
		sql.append(",user_active='"+(user.getStatus().equals("true")?1:2)+"' ");
		sql.append(" WHERE user_id="+user.getUserId());


		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	protected void delete(Connection conn, String userIds)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM user  ");

		sql.append(" WHERE user_id IN ("+userIds+")");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}


	protected User searchById(Connection conn,int id)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM user ");
		sql.append("WHERE user_id ="+id);
		User user = null;
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
			   user = new User();
		        if(rs.next()) {
		        	user.setUserId(rs.getString("user_id"));
		        	user.setUserName(rs.getString("user_name"));
		        	user.setName(rs.getString("user_fname"));
		        	user.setSurName(rs.getString("user_lname"));
		        	user.setPassword(rs.getString("user_password"));
		        	user.setAddress(rs.getString("user_address"));
		        	user.setPhone(rs.getString("user_landlines"));
		        	user.setMobile(rs.getString("user_moblie"));
		        	user.setPeopleId(rs.getString("user_idcard"));
		        	user.setStatus(rs.getString("user_active").equals("1")?"true":"false");
		        }

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return user;
	}
}
